const models = require('../db/db');
const express = require('express');
const router = express.Router();
const mysql = require('mysql');
const $sql = require('../db/sqlMap');
var { Email } = require('../db/config');


const conn = mysql.createConnection(models.mysql);
conn.connect();


// 登录接口
router.post('/login', (req, res) => {
    const user = req.body;
    const sel_email = $sql.user.select + " where email = '" + user.email + "'";
    console.log(sel_email);
    const add_sql_log = $sql.user.addlog;
    conn.query(sel_email, user.email, (error, results) => {
        if (error) {
            throw error;
        }
        console.log(results)
        if (results[0] === undefined) {
            res.send("-1"); // -1 表示查询不到，用户不存在，即邮箱填写错误
        } else {
            if (results[0].email == user.email && results[0].password == user.password && results[0].type == user.type) {
                conn.query(add_sql_log, [user.username, user.email, user.type, user.logtime, user.logcity]);
                res.send("0"); // 0 表示用户存在并且邮箱密码正确且登录类型正确
            } else {
                res.send("1"); // 1 表示用户存在，但密码不正确
            }
        }
    })
});

//获取数据库中总用户数量和管理员数量，并发送到前端
router.post('/sendusernum', (req, res) => {
    const usernum_sql = "select sum(case when type = '用户' THEN 1 ELSE 0 END) as usersum,sum(case when type = '管理员' THEN 1 ELSE 0 END) as govsum from user";
    conn.query(usernum_sql, (err, rst) => {
        if (err) {
            console.log(err)
        } else {
            console.log(rst)
            res.send(rst) // 0 表示用户创建成功
        }
    })
});

//获取数据库中所有用户的详细信息接口
router.post('/senduserlist', (req, res) => {
    const userlist_sql = "select  username, email from `user` where type = '用户'";
    conn.query(userlist_sql, (err, rst) => {
        if (err) {
            console.log(err)
        } else {
            console.log(rst)
            res.send(rst) // 0 表示用户创建成功
        }
    })
});

//发送验证码接口
router.post('/send', (req, res) => {
    const send_user = req.body;
    // console.log(req.body)
    let email = send_user.email;
    console.log(email)
    // let email = '1910260504@qq.com'
    const sel_email = $sql.user.select + " where email = '" + email + "'";
    conn.query(sel_email, email, (error, results) => {
        if (error) {
            throw error;
        }
        console.log(results)
        if (results[0] === undefined) {
            res.send("-1"); // -1 表示查询不到，用户不存在，即邮箱填写错误
        } else {
            let mailOptions = { //发送给用户显示的字段
                from: '1805046917@qq.com',
                to: email,
                subject: '知识地图网站邮箱验证码',
                text: '验证码：' + Email.verify
            }
            Email.transporter.sendMail(mailOptions);
            res.send("0"); // 0 表示用户存在并且邮箱密码正确且登录类型正确
        }
    })
});

//修改密码接口
router.post('/amend', (req, res) => {
    const amend_user = req.body;
    // let email = '1910260504@qq.com'
    // let repassword = 'liu12'
    console.log(amend_user.password);
    const ame_sql = "update user set password = '" + amend_user.password + " 'where email =  '" + amend_user.email + "'";
    conn.query(ame_sql, (err, rst) => {
        if (err) {
            console.log(err);
        } else {
            console.log(rst);
            res.send("0"); // 0 表示用户创建成功
        }
    });
});

// 注册接口
router.post('/add', (req, res) => {
    const params = req.body;
    const sel_sql = $sql.user.select + " where username = '" + params.username + "'";
    const add_sql = $sql.user.add;
    console.log(sel_sql);

    conn.query(sel_sql, params.username, (error, results) => {
        if (error) {
            console.log(err);
        }
        if (results.length != 0 && params.username == results[0].username) {
            res.send("-1"); // -1 表示用户名已经存在
        } else {
            conn.query(add_sql, [params.username, params.email, params.password, "用户"], (err, rst) => {
                if (err) {
                    console.log(err);
                } else {
                    console.log(rst);
                    res.send("0"); // 0 表示用户创建成功
                }
            });
        }
    });
});

//学生路径查询接口
router.post('/studenttrace', (req, res) => {
    const studenttrace_user = req.body.number;
    const studenttrace_detetime = req.body.detetime;
    const studenttrace_action = req.body.action;
    const studenttrace_roomname = req.body.roomname;
    let sql = `select lon,lat,date,buildingname,roomname  from xue12 where 1=1 `
    if (studenttrace_user) {
        sql += `and stunumber='${studenttrace_user}' `
    }
    if (req.body.detetime) {
        sql += `and datetime>='${req.body.detetime[0]}'and datetime<'${req.body.detetime[1]}' `
    }
    if (req.body.action) {
        // console.log(studenttrace_action)
        sql += `and action='${studenttrace_action}' `
    }
    if (studenttrace_roomname) {
        console.log(studenttrace_roomname)
        sql += `and roomname='${studenttrace_roomname}' `
    }


    conn.query(sql, (error, results) => {
        if (error) {
            console.log(err);
        }
        if (results.length == 0) {
            res.send("-1"); // -1 表示数据库中不存在该学生
            console.log("不存在该学生");
        } else {
            console.log(results);
            res.json(results); //将数据封装成json发送给前端
        }
    });



});

// 添加课程
router.post('/addCourse', (req, res) => {
    const params = req.body;
    const sel_sql = $sql.course.select + " where course_name = '" + params.course_name + "'";
    const add_sql = $sql.course.add;
    console.log('/addCourse : ', sel_sql);

    conn.query(sel_sql, params.course_name, (error, results) => {
        if (error) {
            console.log(error);
        }
        if (results.length != 0) {
            res.send("-1"); // -1 表示课程已经存在
        } else {
            conn.query(add_sql, [params.course_name, params.email, params.img_url, params.course_description], (err, rst) => {
                if (err) {
                    console.log(err);
                } else {
                    console.log(rst);
                    res.send("0"); // 0 表示课程创建成功
                }
            });
        }
    });
});

// 选择课程
router.post('/selectCourse', (req, res) => {
    const params = req.body;
    const sel_sql = $sql.course.select + " where email = '" + params.email + "'";
    conn.query(sel_sql, params.email, (error, results) => {
        if (error) {
            console.log(error);
        }
        if (results.length != 0) {
            console.log(results);
            res.send(results); //  0 表示课程查询成功
        }
    });
});

//更新个人信息
router.post('/updatePersonalInfo', (req, res) => {
    const params = req.body;
    const sel_sql = $sql.personal_info.update + " where email = '" + params.email + "'";
    conn.query(sel_sql, [params.name, params.sex, params.birthday, params.identity, params.school, params.education, params.region, params.work_experience], (error, results) => {
        if (error) {
            console.log(error);
        }
        console.log(results);
        if (results.length != 0) {
            console.log(results);
            res.send("1"); //  1 表示更新成z功
        }
    });
});

module.exports = router;